#!/usr/bin/perl

package Migration;

use strict;
use warnings;
use lib "../libs";

use Exporter qw(import);
use JSON;
use File::Find;
use File::Basename;
use Const ();
use App ();
use Utils ();
use Utils qw(dd);
use Output;
use Data::Dumper qw(Dumper);
use Log;
use Time::Piece;
use Encode qw(encode decode);

use utf8;
use open qw(:std :utf8);
binmode STDOUT, ":utf8";

# Enable SQL profiling
$DBI::Profile = 2;

sub list {
    my $migration_path = $Const::CONF_PATH . $Const::DS . "migrations";
    my %migrations;
    find(sub {
        if (-f) {
            my $basename = basename($File::Find::name);
            $migrations{$basename} = decode_json(Utils::readFile($File::Find::name));
        }
    }, $migration_path);

    Log::info("Migration list:");
    for my $key (keys %migrations) {
        my %migration = %{ $migrations{$key} };
        print $migration{src}->{host} . "\n";
        Log::info("> Src: type: $migration{src}->{host}, port: $migration{src}->{port}, user: $migration{src}->{user}, pass: $migration{src}->{pass}");
        Log::info("< Dst: type: $migration{dst}->{host}, port: $migration{dst}->{port}, user: $migration{dst}->{user}, pass: $migration{dst}->{pass}");

        unless (exists($migration{'tables'})) {
            Log::warn("- No tables to migrate!");
            next;
        }
        Log::info("- Tables:");
        my @tables = @{ $migration{'tables'} };
        for my $table (@tables) {
            print "\t$table\n";
        }
    }
}

sub run 
{
    my $migration_path = $Const::CONF_PATH . $Const::DS . "migrations";
    my %migrations;

    find(sub {
        if (-f) {
            my $basename = basename($File::Find::name);
            unless ($basename =~ /\.json$/) {
                Log::warn("Skip file: $basename");
                next;
            }
            $migrations{$basename} = decode_json(Utils::readFile($File::Find::name));
        }
    }, $migration_path);
                    

    for my $key (keys %migrations) {
        my %migration = %{ $migrations{$key} };
        Log::info(">>>>>>>> Migration file: $key <<<<<<<<");

        my $max_processes = 3; # Maximum number of parallel processes
        my @pids;
        my @tables = @{ $migration{'tables'} };
        my $table_count = scalar(@tables);

        for (my $i = 0; $i < $table_count; $i++) {
            my $table = $tables[$i];
            Log::info("Table: $table. in [ $i/$table_count ]");

            if (scalar(@pids) < $max_processes) {
                my $pid = fork();
                if ($pid == 0) {
                    my $mypid = $$;

                    Log::info("---- Child process [$mypid] started for table ${table}. ----");
                    my $src_db = App::getDB($migration{'src'});
                    my $dst_db = App::getDB($migration{'dst'});

                    Output::init();

                    # ---------------- diff count start ----------------
                    # Compare row count
                    Log::info("> Start migrate table [$table] data count.");
                    my $src_count = App::getCount($src_db, $table);
                    my $dst_count = App::getCount($dst_db, $table);
                    Log::info("  Src count: $src_count, Dst count: $dst_count");
                    if ($src_count != $dst_count) {
                        Log::warn("  Table [$table] data count mismatch!");
                    } else {
                        Log::primary("  Table [$table] data count match!");
                    }
                    # ---------------- diff count end ----------------

                    # Get columns
                    my $src_columns = $src_db->{dbh}->selectall_arrayref("SELECT column_name, data_type, data_length, nullable FROM user_tab_columns WHERE table_name = '${table}' ORDER BY column_id");

                    # ---------------- diff sum start ----------------
                    # Compare sum for NUMBER columns
                    my $diff_sum = 1;
                    my @diff_sum_result;
                    Log::info("> Start migrate table [$table] data sum.");
                    my @src_number_columns;
                    for my $column (@$src_columns) {
                        my ($column_name, $data_type, $data_length, $nullable) = @$column;
                        if ($data_type eq "NUMBER") {
                            push(@src_number_columns, $column_name);
                        }
                    }
                    Log::info("    Src table [$table] number columns: " . join(", ", @src_number_columns));
                    for my $number_column (@src_number_columns) {
                        Log::info(">> Start migrate table [$table] column [$number_column] sum.");
                        my $src_sum = App::getSum($src_db, $table, $number_column);
                        my $dst_sum = App::getSum($dst_db, $table, $number_column);
                        Log::info("  Src sum: $src_sum, Dst sum: $dst_sum");
                        if ($src_sum != $dst_sum) {
                            Log::warn("  Table [$table] column [$number_column] sum mismatch!");
                            $diff_sum = 0;
                        } else {
                            Log::primary("  Table [$table] column [$number_column] sum match!");
                        }
                        
                        push (@diff_sum_result, {
                            collumn => $number_column,
                            src_sum => $src_sum,
                            dst_sum => $dst_sum
                        })
                    }
                    # ---------------- diff sum end ----------------

                    # ---------------- diff row start ----------------
                    # Compare rows for VARCHAR2 columns
                    Log::info("> Start migrate table [$table] data for row.");
                    my @src_varchar_columns;
                    for my $column (@$src_columns) {
                        my ($column_name, $data_type, $data_length, $nullable) = @$column;
                        if ($data_type eq "VARCHAR2") {
                            push(@src_varchar_columns, $column_name);
                        }
                    }
                    Log::info("  Src table [$table] varchar columns: " . join(", ", @src_varchar_columns));

                    # Get a base row
                    # my $base_row = App::getRow($src_db, $table, "ROWNUM=1");
                    my $base_row = App::getBaseRow($src_db, $table);
                    # dd($base_row);

                    # Generate conditions for detailed comparison
                    my @conditions = ();
                    my $canvalidate = 0;
                    my $sql = "SELECT COUNT(*) AS COUNT FROM $table WHERE 1=1";
                    for my $varchar_column (@src_varchar_columns) {
                        if (defined($base_row->{$varchar_column})) {
                            my $value = $base_row->{$varchar_column};
                            my $where = "$varchar_column = '$value'";
                            $sql .= " AND $where";
                            push(@conditions, $where);
                            my $count = App::selectRow($src_db, $sql)->{COUNT};
                            if ($count < 100){
                                $canvalidate = 1;
                                last;
                            }
                        }
                    }

                    my $match;
                    my @diff_row_result;
                    my $diff_row = 1;
                    if ($canvalidate) {
                        # Perform detailed comparison
                        $sql = "SELECT * FROM $table WHERE 1=1 AND " . join(" AND ", @conditions) . " ORDER BY " . join(", ", @src_varchar_columns);
                        my $src_rows = App::exec($src_db, $sql);
                        my $dst_rows = App::exec($dst_db, $sql);
                        # Compare rows
                        ($match, @diff_row_result) = compare_rows($src_rows, $dst_rows, \@src_varchar_columns);
                        if ($match) {
                            Log::primary("  Data matches for table $table.");
                        } else {
                            Log::warn("  Data mismatch for table $table.");
                            $diff_row = 0;
                        }
                    } else {
                        Log::info("Too many rows to perform detailed comparison for table $table.");
                    }
                    # ---------------- diff row end ----------------

                    # ---------------- output start ----------------
                    Log::info("Generate output excels for table [ $table ].");
                    Output::setOutputDir();

                    my $subWorkbook = Output::subInit($table);

                    my $sum_worksheet = $subWorkbook->add_worksheet("sum");
                    $sum_worksheet->write(0, 0, "src host");
                    $sum_worksheet->write(0, 1, "src port");
                    $sum_worksheet->write(0, 2, "src user");
                    $sum_worksheet->write(0, 3, "dst host");
                    $sum_worksheet->write(0, 4, "dst port");
                    $sum_worksheet->write(0, 5, "dst user");
                    $sum_worksheet->write(0, 6, "dst database");
                    $sum_worksheet->write(0, 7, "table");
                    $sum_worksheet->write(0, 8, "column");
                    $sum_worksheet->write(0, 9, "src sum");
                    $sum_worksheet->write(0, 10, "dst sum");
                    $sum_worksheet->write(0, 11, "diff sum result");
                    # $sum_worksheet->write(0, 12, "execute time");
                    for my $i (0..$#diff_sum_result) {
                        my $row = $i + 1;
                        $sum_worksheet->write($row, 0, $migration{'src'}->{host});
                        $sum_worksheet->write($row, 1, $migration{'src'}->{port});
                        $sum_worksheet->write($row, 2, $migration{'src'}->{user});
                        $sum_worksheet->write($row, 3, $migration{'dst'}->{host});
                        $sum_worksheet->write($row, 4, $migration{'dst'}->{port});
                        $sum_worksheet->write($row, 5, $migration{'dst'}->{user});
                        $sum_worksheet->write($row, 6, $migration{'dst'}->{database});
                        $sum_worksheet->write($row, 7, $table);
                        $sum_worksheet->write($row, 8, $diff_sum_result[$i]->{collumn});
                        $sum_worksheet->write($row, 9, $diff_sum_result[$i]->{src_sum});
                        $sum_worksheet->write($row, 10, $diff_sum_result[$i]->{dst_sum});
                        $sum_worksheet->write($row, 11, $diff_sum_result[$i]->{src_sum} == $diff_sum_result[$i]->{dst_sum}? 1 : 0);
                    }

                    my $row_worksheet = $subWorkbook->add_worksheet("row");
                    $row_worksheet->write(0, 0, "src host");
                    $row_worksheet->write(0, 1, "src port");
                    $row_worksheet->write(0, 2, "src user");
                    $row_worksheet->write(0, 3, "dst host");
                    $row_worksheet->write(0, 4, "dst port");
                    $row_worksheet->write(0, 5, "dst user");
                    $row_worksheet->write(0, 6, "dst database");
                    $row_worksheet->write(0, 7, "table");
                    my $columns_count = scalar(@src_varchar_columns);
                    for my $i (0..$#src_varchar_columns) {
                        $row_worksheet->write(0, ($i*3)+8, "src_col[$src_varchar_columns[$i]]");
                        $row_worksheet->write(0, ($i*3)+9, "dst_col[$src_varchar_columns[$i]]");
                        $row_worksheet->write(0, ($i*3)+10, "diff_col[$src_varchar_columns[$i]]");
                    }
                    for my $i (0..$#diff_row_result) {
                        my $row = $i + 1;
                        $row_worksheet->write($row, 0, $migration{'src'}->{host});
                        $row_worksheet->write($row, 1, $migration{'src'}->{port});
                        $row_worksheet->write($row, 2, $migration{'src'}->{user});
                        $row_worksheet->write($row, 3, $migration{'dst'}->{host});
                        $row_worksheet->write($row, 4, $migration{'dst'}->{port});
                        $row_worksheet->write($row, 5, $migration{'dst'}->{user});
                        $row_worksheet->write($row, 6, $migration{'dst'}->{database});
                        $row_worksheet->write($row, 7, $table);
                        
                        for my $j (0..$#{$diff_row_result[$i]}) {
                            $row_worksheet->write($row, $j+8, $diff_row_result[$i]->[$j]);
                        }
                    }

                    $subWorkbook->close();

                    my $workbook = Output::init();
                    my $count_worksheet = $workbook->add_worksheet("count");
                    $count_worksheet->write(0, 0, "src host");
                    $count_worksheet->write(0, 1, "src port");
                    $count_worksheet->write(0, 2, "src user");
                    $count_worksheet->write(0, 3, "dst host");
                    $count_worksheet->write(0, 4, "dst port");
                    $count_worksheet->write(0, 5, "dst user");
                    $count_worksheet->write(0, 6, "dst database");
                    $count_worksheet->write(0, 7, "table");
                    $count_worksheet->write(0, 8, "src count");
                    $count_worksheet->write(0, 9, "dst count");
                    $count_worksheet->write(0, 10, "diff count");
                    # $count_worksheet->write(0, 11, "execute time");
                    $count_worksheet->write(0, 11, "table link");
                    $count_worksheet->write(0, 12, "diff sum");
                    $count_worksheet->write(0, 13, "diff row");
                    $count_worksheet->write(1, 0, $migration{'src'}->{host});
                    $count_worksheet->write(1, 1, $migration{'src'}->{port});
                    $count_worksheet->write(1, 2, $migration{'src'}->{user});
                    $count_worksheet->write(1, 3, $migration{'dst'}->{host});
                    $count_worksheet->write(1, 4, $migration{'dst'}->{port});
                    $count_worksheet->write(1, 5, $migration{'dst'}->{user});
                    $count_worksheet->write(1, 6, $migration{'dst'}->{database});
                    $count_worksheet->write(1, 7, $table);
                    $count_worksheet->write(1, 8, $src_count);
                    $count_worksheet->write(1, 9, $dst_count);
                    $count_worksheet->write(1, 10, $src_count != $dst_count ? 1 : 0);
                    # $count_worksheet->write(1, 11, "");
                    $count_worksheet->write_url(1, 11, $table. ".xlsx");
                    $count_worksheet->write(1, 12, $diff_sum? 1 : 0);
                    $count_worksheet->write(1, 13, $diff_row? 1 : 0);
                    $workbook->close();
                    # ---------------- output end ----------------

                    exit 0;
                } else {
                    push(@pids, $pid);
                }
            }
        }

        for my $pid (@pids) {
            waitpid($pid, 0);
        }
    }

}

sub compare_rows {
    my ($src_rows, $dst_rows, $src_varchar_columns) = @_;
    
    # if (scalar(@$src_rows) != scalar(@$dst_rows)) {
    #     Log::warn("  Row column mismatch: ". scalar(@$src_rows). " (src) vs ". scalar(@$dst_rows). " (dst)");
    #     return 0; # Mismatch
    # }

    my $match = 1;

    my @diff_row_result;

    for my $i (0..$#$src_rows) {
        my $src_row = $src_rows->[$i];
        my $dst_row = $dst_rows->[$i];
        
        my @row_result;
        for my $varchar_column (@$src_varchar_columns) {
            my $src_value = defined($src_row->{$varchar_column})? $src_row->{$varchar_column} : $src_row->{lc($varchar_column)}; 
            my $dst_value = defined($dst_row->{$varchar_column})? $dst_row->{$varchar_column} : $dst_row->{lc($varchar_column)};
            if ($src_value ne $dst_value) {
                Log::warn("  Row [${i}], column [$varchar_column] mismatch! src: $src_value, dst: $dst_value");
                $match = 0;
            } else {
                Log::info("  Row [${i}], column [$varchar_column] match! src: $src_value, dst: $dst_value"); 
            }
            push(@row_result, (
                $src_value,
                $dst_value,
                $src_value eq $dst_value? 1 : 0
            ));
        }
        push(@diff_row_result, \@row_result);
    }

    return $match, @diff_row_result;
}


sub parse_date {
    my ($date) = @_;
    if ($date =~ /^(\d{2})-(\w{3})-(\d{2})$/) {
        my ($day, $mon, $year) = ($1, $2, $3);
        my %mon_map = (
            'JAN' => '01', 'FEB' => '02', 'MAR' => '03', 'APR' => '04',
            'MAY' => '05', 'JUN' => '06', 'JUL' => '07', 'AUG' => '08',
            'SEP' => '09', 'OCT' => '10', 'NOV' => '11', 'DEC' => '12'
        );
        $mon = $mon_map{uc($mon)};
        $year = "20$year"; 
        return "$year-$mon-$day";
    } elsif ($date =~ /^(\d{4})-(\d{2})-(\d{2})$/) {
        return $date;
    } else {
        die "Unknown date format: $date";
    }
}



1;

__END__
